package com.javasm.dao.MisMannageDao.impl;


import com.javasm.dao.MisMannageDao.ProdDao;
import com.javasm.dao.MisMannageDao.SeriesDao;
import com.javasm.dao.MisMannageDao.TypeDao;
import com.javasm.entity.MisMannageEntity.ProdInfo;
import com.javasm.entity.MisMannageEntity.User;
import com.javasm.util.JDBCUtil;

import java.math.BigDecimal;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @author:JAVASM
 * @description:
 * @date:2022/5/23 20:43
 * @version:0.1
 * @since:jdk11
 */
public class ProdDaoImpl implements ProdDao {

//    @Override
//    public Integer setAuditStatus(Integer setStatusProdId,String isPass) {
//
//        Connection conn = JDBCUtil.getConn();
//        PreparedStatement ps = null;
//
//        StringBuilder sb = new StringBuilder();
//        sb.append("update tb_prod_info set audit_status=? where prod_id=?");
//
//        Integer editStatus = 0;
//        try {
//            ps = conn.prepareStatement(sb.toString());
//            ps.setString(1,isPass);
//            ps.setInt(2,setStatusProdId);
//            editStatus = ps.executeUpdate();
//
//        } catch (SQLException throwables) {
//            throwables.printStackTrace();
//        } finally {
//            JDBCUtil.closeConn(conn, ps,null, rs);
//        }
//
//        return editStatus;
//    }

    @Override
    public String getReviewerName(Integer reviewerId) {
        Connection conn = JDBCUtil.getConn();
        PreparedStatement ps = null;
        ResultSet rs = null;

        StringBuilder sb = new StringBuilder();
        sb.append("select user_name from tb_user where user_id=?");

        String userName = null;
        try {
            ps = conn.prepareStatement(sb.toString());
            ps.setInt(1,reviewerId);
            rs = ps.executeQuery();
            while (rs.next()) {
                userName = rs.getString("user_name");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
           JDBCUtil.closeConn(conn, ps,null, rs);
        }
        return userName;

    }

    @Override
    public ProdInfo getProdById(Integer queryProdId) {
        Connection conn = JDBCUtil.getConn();
        PreparedStatement ps = null;
        ResultSet rs = null;

        StringBuilder sb = new StringBuilder();
        sb.append("select series_id,second_type_id,prod_name,remit_company,rate_year,\n" +
                "money_type,opening_time,subscribe_cycle,fund_manage_rate,subscribe_rate,\n" +
                "money_submit_style,redeem_cycle,redeem_min_money,redeem_fee,closed_cycle,\n" +
                "min_money,reviewer_id\n" +
                "from tb_prod_info \n" +
                "where prod_id=?");

        ProdInfo queryProd = new ProdInfo();
        try {
            ps = conn.prepareStatement(sb.toString());
            ps.setInt(1,queryProdId);
            rs = ps.executeQuery();

            while (rs.next()) {
                Integer seriesId = rs.getInt("series_id");
                queryProd.setSeriesId(seriesId);
                Integer secondTypeId = rs.getInt("second_type_id");
                queryProd.setSecondTypeId(secondTypeId);
                String prodName = rs.getString("prod_name");
                queryProd.setProdName(prodName);
                String remitCompany = rs.getString("remit_company");
                queryProd.setRemitCompany(remitCompany);
                String rateYear = rs.getString("rate_year");
                queryProd.setRateYear(rateYear);
                String moneyType = rs.getString("money_type");
                queryProd.setMoneyType(moneyType);
                Date openingTime = rs.getDate("opening_time");
                queryProd.setOpeningTime(openingTime);
                Integer subscribeCycle = rs.getInt("subscribe_cycle");
                queryProd.setSubscribeCycle(subscribeCycle);
                String fundManageRate = rs.getString("fund_manage_rate");
                queryProd.setFundManageRate(fundManageRate);
                String subscribeRate = rs.getString("subscribe_rate");
                queryProd.setSubscribeRate(subscribeRate);
                String moneySubmitStyle = rs.getString("money_submit_style");
                queryProd.setMoneySubmitStyle(moneySubmitStyle);
                Integer redeemCycle = rs.getInt("redeem_cycle");
                queryProd.setRedeemCycle(redeemCycle);
                BigDecimal redeemMinMoney = rs.getBigDecimal("redeem_min_money");
                queryProd.setRedeemMinMoney(redeemMinMoney);
                BigDecimal redeemFee = rs.getBigDecimal("redeem_fee");
                queryProd.setRedeemFee(redeemFee);
                Integer closedCycle = rs.getInt("closed_cycle");
                queryProd.setClosedCycle(closedCycle);
                BigDecimal minMoney = rs.getBigDecimal("min_money");
                queryProd.setMinMoney(minMoney);
                Integer reviewerId = rs.getInt("reviewer_id");
                queryProd.setReviewerId(reviewerId);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtil.closeConn(conn, ps,null, rs);
        }
        return queryProd;
    }

    @Override
    public Integer editProd(ProdInfo editProd) {
        Connection conn = JDBCUtil.getConn();
        PreparedStatement ps = null;

        StringBuilder sb = new StringBuilder();
        sb.append("update tb_prod_info set\n" +
                "second_type_id=?,prod_name=?,remit_company=?,rate_year=?,money_type=?,\n" +
                "opening_time=?,subscribe_cycle=?,fund_manage_rate=?,subscribe_rate=?,money_submit_style=?,\n" +
                "redeem_cycle=?,redeem_min_money=?,redeem_fee=?,closed_cycle=?,min_money=?,\n" +
                "audit_status='审核中' where prod_id=?");
        Integer updateNum = null;

        try {
            ps = conn.prepareStatement(sb.toString());
            ps.setInt(1,editProd.getSecondTypeId());
            ps.setString(2,editProd.getProdName());
            ps.setString(3,editProd.getRemitCompany());
            ps.setString(4,editProd.getRateYear());
            ps.setString(5,editProd.getMoneyType());
            if (editProd.getOpeningTime()!=null){
                Date openingTimeDate = new Date(editProd.getOpeningTime().getTime());
                ps.setDate(6, openingTimeDate);
            } else {
                ps.setDate(6, null);
            }
            ps.setInt(7,editProd.getSubscribeCycle());
            ps.setString(8,editProd.getFundManageRate());
            ps.setString(9,editProd.getSubscribeRate());
            ps.setString(10,editProd.getMoneySubmitStyle());
            ps.setInt(11,editProd.getRedeemCycle());
            ps.setBigDecimal(12,editProd.getRedeemMinMoney());
            ps.setBigDecimal(13,editProd.getRedeemFee());
            ps.setInt(14,editProd.getClosedCycle());
            ps.setBigDecimal(15,editProd.getMinMoney());
            ps.setInt(16,editProd.getProdId());

            updateNum = ps.executeUpdate();

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtil.closeConn(conn, ps,null, null);
        }
        return updateNum;
    }

    @Override
    public Integer insertAsset(ProdInfo assetProd) {
        Connection conn = JDBCUtil.getConn();
        PreparedStatement ps = null;

        StringBuilder sb = new StringBuilder();
        sb.append("update tb_prod_info set asset_value=?,asset_base_date=?,count_growth_rate=? where prod_id=?");
        Integer addAssetNum = null;
        try {
            ps = conn.prepareStatement(sb.toString());
            ps.setBigDecimal(1,assetProd.getAssetValue());

            Date assetBaseDateSql = new Date(assetProd.getAssetBaseDate().getTime());
            ps.setDate(2, assetBaseDateSql);

            ps.setString(3,assetProd.getCountGrowthRate());
            ps.setInt(4,assetProd.getProdId());

            addAssetNum = ps.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtil.closeConn(conn, ps,null, null);
        }
        return addAssetNum;
    }

    @Override
    public List<User> getUserToAudit(Integer userId) {
        Connection conn = JDBCUtil.getConn();
        PreparedStatement ps = null;
        ResultSet rs = null;

        StringBuilder sb = new StringBuilder();
        sb.append("select tu.user_id,tu.user_name from tb_user tu where tu.user_id<>? and tu.user_role_id=1");


        List<User> userInfoList = new ArrayList<User>();
        try {
            ps = conn.prepareStatement(sb.toString());
            ps.setInt(1,userId);
            rs = ps.executeQuery();

            while (rs.next()) {
                Integer userId1 = rs.getInt("user_id");
                String userName = rs.getString("user_name");
                User userInfo = new User();
                userInfo.setUserId(userId1);
                userInfo.setUserName(userName);
                userInfoList.add(userInfo);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtil.closeConn(conn, ps,null, rs);
        }
        return userInfoList;

    }

    @Override
    public Integer insertProd(ProdInfo insertProd) {
        Connection conn = JDBCUtil.getConn();
        PreparedStatement ps = null;
        StringBuilder sb = new StringBuilder();
        sb.append("insert into \n" +
                "tb_prod_info(series_id,second_type_id,prod_name,remit_company,rate_year,\n" +
                "money_type,opening_time,subscribe_cycle,fund_manage_rate,subscribe_rate,\n" +
                "money_submit_style,redeem_cycle,redeem_min_money,redeem_fee,closed_cycle,\n" +
                "reviewer_id,create_user_id,audit_status,min_money)\n" +
                "values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
        Integer addProdNum = 0;
        try {
            ps = conn.prepareStatement(sb.toString());
            ps.setInt(1,insertProd.getSeriesId());
            ps.setInt(2,insertProd.getSecondTypeId());
            ps.setString(3,insertProd.getProdName());
            ps.setString(4,insertProd.getRemitCompany());
            ps.setString(5,insertProd.getRateYear());
            ps.setString(6,insertProd.getMoneyType());

            if (insertProd.getOpeningTime()==null){
                ps.setDate(7, null);
            }else {
                Date openingTimeSql = new Date(insertProd.getOpeningTime().getTime());
                ps.setDate(7, openingTimeSql);
            }



            ps.setInt(8,insertProd.getSubscribeCycle());
            ps.setString(9,insertProd.getFundManageRate());
            ps.setString(10,insertProd.getSubscribeRate());
            ps.setString(11,insertProd.getMoneySubmitStyle());
            ps.setInt(12,insertProd.getRedeemCycle());
            ps.setBigDecimal(13,insertProd.getRedeemMinMoney());
            ps.setBigDecimal(14,insertProd.getRedeemFee());
            ps.setInt(15,insertProd.getClosedCycle());
            ps.setInt(16,insertProd.getReviewerId());
            ps.setInt(17,insertProd.getCreateUserId());
            ps.setString(18,"审核中");
            ps.setBigDecimal(19,insertProd.getMinMoney());
            addProdNum = ps.executeUpdate();

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtil.closeConn(conn, ps,null, null);
        }
        return addProdNum;
    }

    @Override
    public Integer prodTotalNum(ProdInfo queryProd, Integer userId) {
        Connection conn = JDBCUtil.getConn();
        PreparedStatement ps = null;
        ResultSet rs = null;

        StringBuilder sb = new StringBuilder();
        sb.append("select count(1) as prodTotalNum from tb_prod_info tpi ");

        sb.append("where tpi.create_user_id=? ");
        List paramList = new ArrayList();

        paramList.add(userId);

        if (queryProd.getProdName()!=null && !"".equals(queryProd.getProdName())){
            sb.append("and tpi.prod_name like ? ");
            paramList.add("%"+queryProd.getProdName()+"%");
        }
        if (queryProd.getAuditStatus()!=null && !"".equals(queryProd.getAuditStatus())){
            sb.append("and tpi.audit_status=? ");
            paramList.add(queryProd.getAuditStatus());
        }
        if (queryProd.getSecondTypeId()!=0){
            sb.append("and tpi.second_type_id=? ");
            paramList.add(queryProd.getSecondTypeId());
        }


        Integer prodTotalNum = 0;
        try {
            ps = conn.prepareStatement(sb.toString());
            for (int i = 0; i < paramList.size(); i++) {
                ps.setObject(i+1,paramList.get(i));
            }
            rs = ps.executeQuery();

            while (rs.next()) {
                prodTotalNum = rs.getInt("prodTotalNum");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JDBCUtil.closeConn(conn, ps,null, rs);
        }
        return prodTotalNum;

    }

    @Override
    public List<ProdInfo> getProdByPage(Integer page, Integer pageSize, ProdInfo queryProd,Integer userId) {
        Connection conn = JDBCUtil.getConn();
        PreparedStatement ps = null;
        ResultSet rs = null;

        StringBuilder sb = new StringBuilder();
        sb.append("select tpi.prod_id,tpi.prod_name,tpi.series_id,tpi.second_type_id,tpi.opening_time,tpi.money_type," +
                "tpi.audit_status,tpi.remit_company,tpi.reviewer_id from tb_prod_info tpi ");

        sb.append("where tpi.create_user_id=? ");
        List paramList = new ArrayList();

        paramList.add(userId);

        if (queryProd.getProdName()!=null && !"".equals(queryProd.getProdName())){
            sb.append("and tpi.prod_name like ? ");
            paramList.add("%"+queryProd.getProdName()+"%");
        }
        if (queryProd.getAuditStatus()!=null && !"".equals(queryProd.getAuditStatus())){
            sb.append("and tpi.audit_status= ? ");
            paramList.add(queryProd.getAuditStatus());
        }
        if (queryProd.getSecondTypeId()!=0){
            sb.append("and tpi.second_type_id=? ");
            paramList.add(queryProd.getSecondTypeId());
        }

        sb.append("order by tpi.prod_update_time desc,tpi.prod_id asc ");
        sb.append("limit "+(page-1)*pageSize+","+pageSize);

        List<ProdInfo> prodInfoList = new ArrayList<ProdInfo>();
        try {
            ps = conn.prepareStatement(sb.toString());
            for (int i = 0; i < paramList.size(); i++) {
                ps.setObject(i+1,paramList.get(i));
            }
            rs = ps.executeQuery();
            while (rs.next()) {
                String prodName = rs.getString("prod_name");
                Integer seriesId = rs.getInt("series_id");
                Integer secondTypeId = rs.getInt("second_type_id");
                Date openingTime = rs.getDate("opening_time");
                String moneyType = rs.getString("money_type");
                String auditStatus = rs.getString("audit_status");
                String remitCompany = rs.getString("remit_company");
                Integer prodId = rs.getInt("prod_id");
                Integer reviewerId = rs.getInt("reviewer_id");

                TypeDao td = new TypeDaoImpl();
                String typeName = td.getTypeName(secondTypeId);
                SeriesDao sd = new SeriesDaoImpl();
                String seriesName = sd.getSeriesNameById(seriesId);


                ProdInfo prodInfo = new ProdInfo();
                prodInfo.setProdName(prodName);
                /*prodInfo.setSeriesId(seriesId);
                prodInfo.setSecondTypeId(secondTypeId);*/
                prodInfo.setOpeningTime(openingTime);
                prodInfo.setMoneyType(moneyType);
                prodInfo.setAuditStatus(auditStatus);
                prodInfo.setRemitCompany(remitCompany);
                prodInfo.setSecondTypeName(typeName);
                prodInfo.setSeriesName(seriesName);
                prodInfo.setProdId(prodId);
                prodInfo.setReviewerId(reviewerId);
                prodInfoList.add(prodInfo);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtil.closeConn(conn, ps,null, rs);
        }
        return prodInfoList;

    }
}
